Release 10.1A: OpenEdge Data Management:
SQL Reference

CREATE VIEW

Creates a view with the specified name on existing tables or views.

Syntax

CREATE VIEW [ owner_name.]view_name [ ( column_name, column_name,... ) ] AS [ ( ] query_expression [ ) ] [ WITH CHECK OPTION ] ;

owner_name

Owner of the created view.

( column_name, column_name,... )

Specifies column names for the view. These names provide an alias for the columns selected by the query specification. If the column names are not specified, then the view is created with the same column names as the tables or views on which it is based.

WITH CHECK OPTION

Checks that the updated or inserted row satisfies the view definition. The row must be selectable using the view. The WITH CHECK OPTION clause is only allowed on an updatable view.

Notes

A view is deletable if deleting rows from that view is allowed. For a view to be deletable, the view definition has to satisfy the following conditions:
The first FROM clause contains only one table reference or one view reference.
There are no aggregate functions, DISTINCT clause, GROUP BY clause, or HAVING clause in the view definition.
If the first FROM clause contains a view reference, then the view referred to is deletable.
A view is updatable if updating rows from that view is allowed. For a view to be updatable, the view has to satisfy the following conditions:
The view is deletable (it satisfies all the conditions specified above for deletability).
All the select expressions in the first SELECT clause of the view definition are simple column references.
If the first FROM clause contains a view reference, then the view referred to is updatable.
A view is insertable if inserting rows into that view is allowed. For a view to be insertable, the view has to satisfy the following conditions:
The view is updatable (it satisfies all the conditions specified above for update ability).
If the first FROM clause contains a table reference, then all NOT NULL columns of the table are selected in the first SELECT clause of the view definition.
If the first FROM clause contains a view reference, then the view referred to is insertable.

Example

CREATE VIEW ne_customers AS SELECT name, address, city, state FROM customer WHERE state IN ( 'NH', 'MA', 'ME', 'RI', 'CT', 'VT' ) WITH CHECK OPTION ; CREATE VIEW OrderCount (custnum, numorders) AS SELECT CustNum, COUNT(*) FROM Order GROUP BY CustNum;

Authorization

Must have DBA privilege, RESOURCE privilege, or SELECT privilege.

Related statements

DROP VIEW